package lance.honeydew.dao;

import java.util.ArrayList;
import java.util.List;

import lance.honeydew.bean.CategoryMedium;
import lance.honeydew.bean.ItemInformation;
import lance.honeydew.bean.ItemStock;
import lance.honeydew.bean.PublishMessage;
import lance.honeydew.bean.ShoppingMoney;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;

public class ItemInformationDAO extends RootDAO {
	
	private static final Logger logger = Logger.getLogger(ItemInformationDAO.class);
	
	/**
	 * queryItemStockByItemInformationOfNumber
	 * @param number
	 * @return
	 */
	public List<ItemStock> queryItemStockByItemInformationOfNumber(String number) {
		
		List<ItemStock> result = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 " + 
					" and i.Number = '" + number + "' ";
			
			
			Session session = getSession();
			ItemInformation itemInfoObj = (ItemInformation) session.createQuery(sqlString).list().get(0);
			session.close();
			
			if (itemInfoObj != null) {
				if (CollectionUtils.isNotEmpty(itemInfoObj.getItemStockSet())) {
					result = new ArrayList<ItemStock>(itemInfoObj.getItemStockSet());
				}
			}
			
		} catch(Exception e) {
			logger.debug(e);
		}
		
		return result;
	}
	
	/**
	 * findItemInformationByNumber
	 * @param number
	 * @return
	 */
	public ItemInformation findItemInformationByNumber(String number) {
		
		ItemInformation result = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 " + 
					" and i.Number = " + number + " ";
			
			
			Session session = getSession();
			result = (ItemInformation)session.createQuery(sqlString).list().get(0);
			session.close();
			
		} catch(Exception e) {
			logger.debug(e);
		}
		
		return result;
	}

	/**
	 * findItemInformationBySeqId
	 * @param seqId
	 * @return
	 */
	public ItemInformation findItemInformationBySeqId(Long seqId) {
		
		ItemInformation result = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " +  
					" where 1=1 " + 
					" and i.Seqid = " + seqId + " ";
			
			
			Session session = getSession();
			result = (ItemInformation)session.createQuery(sqlString).list().get(0);
			session.close();
			
		} catch(Exception e) {
			logger.debug(e);
		}
		
		return result;
	}
	
	/**
	 * queryItemInformationByQb
	 * @param qb
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public List<ItemInformation> queryItemInformationByQb(ItemInformation qb, int pageNum, int pageSize) {
		
		List<ItemInformation> list = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 ";
			
			if (StringUtils.isNotEmpty(qb.getNumber())) {
				sqlString += " and i.Number = " + qb.getNumber() + " ";
			}
			
			if (StringUtils.isNotEmpty(qb.getName())) {
				sqlString += " and i.Name like '%" + qb.getName() + "%' ";
			}
			
			Session session = getSession();
			list = (List<ItemInformation>)session.createQuery(sqlString).list();
			session.close();
			
		} catch(Exception e) {
			e.printStackTrace();
			logger.debug(e);
		}
		
		return list;
	}
	
	/**
	 * queryItemInformationByIndexQuery
	 * @param qb
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public List<ItemInformation> queryItemInformationByIndexQuery(Long categoryBigSeqid, Long categorymediumSeqid, int pageNum, int pageSize) {
		
		List<ItemInformation> list = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 ";
			
			sqlString += " and b.Seqid = " + categoryBigSeqid + " ";
			sqlString += " and m.Seqid = " + categorymediumSeqid + " ";
			
			sqlString += " and current_timestamp between i.Startdate and i.Enddate ";
			
			Session session = getSession();
			list = (List<ItemInformation>)session.createQuery(sqlString)
					.setFirstResult((pageNum - 1) * pageSize)
					.setMaxResults(pageSize)
					.list();
			session.close();
			
		} catch(Exception e) {
			e.printStackTrace();
			logger.debug(e);
		}
		
		return list;
	}
	
	/**
	 * queryItemInformationByIndexQueryAll
	 * @param qb
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public List<ItemInformation> queryItemInformationByIndexQueryAll(String itemName, Long categoryBigSeqid, Long categorymediumSeqid
			, int pageNum, int pageSize) {
		
		List<ItemInformation> list = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 ";
			
			sqlString += " and current_timestamp between i.Startdate and i.Enddate ";
			sqlString += " and i.Name Like '%" + itemName + "%' ";
			
			Session session = getSession();
			list = (List<ItemInformation>)session.createQuery(sqlString)
					.setFirstResult((pageNum - 1) * pageSize)
					.setMaxResults(pageSize)
					.list();
			session.close();
			
		} catch(Exception e) {
			e.printStackTrace();
			logger.debug(e);
		}
		
		return list;
	}
	
	/**
	 * countItemInformationByIndexQuery
	 * @param categoryBigSeqid
	 * @param categorymediumSeqid
	 * @return
	 */
	public int countItemInformationByIndexQuery(Long categoryBigSeqid, Long categorymediumSeqid) {
		
		int count = 0;
		List<ItemInformation> list = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 ";
			
			sqlString += " and b.Seqid = " + categoryBigSeqid + " ";
			sqlString += " and m.Seqid = " + categorymediumSeqid + " ";
			
			sqlString += " and current_timestamp between i.Startdate and i.Enddate ";
			
			Session session = getSession();
			list = (List<ItemInformation>)session.createQuery(sqlString).list();
			if (list != null) {
				count = list.size();
			}
			session.close();
			
		} catch(Exception e) {
			e.printStackTrace();
			logger.debug(e);
		}
		
		return count;
	}
	
	/**
	 * countItemInformationByIndexQueryAll
	 * @param categoryBigSeqid
	 * @param categorymediumSeqid
	 * @return
	 */
	public int countItemInformationByIndexQueryAll(String itemName, 
			Long categoryBigSeqid, Long categorymediumSeqid) {
		
		int count = 0;
		List<ItemInformation> list = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 ";
			
			sqlString += " and current_timestamp between i.Startdate and i.Enddate ";
			sqlString += " and i.Name Like '%" + itemName + "%' ";
			
			Session session = getSession();
			list = (List<ItemInformation>)session.createQuery(sqlString).list();
			if (list != null) {
				count = list.size();
			}
			session.close();
			
		} catch(Exception e) {
			e.printStackTrace();
			logger.debug(e);
		}
		
		return count;
	}
	
	/**
	 * findItemInformationByIndexInside
	 * @param seqId
	 * @return
	 */
	public ItemInformation findItemInformationByIndexInside(Long seqId) {
		
		ItemInformation result = null;
		
		try {
			
			String sqlString = " select i from ItemInformation i " +
					" inner join fetch i.Categorybigseqid b  " +
					" inner join fetch i.Categorymedium m  " + 
					" where 1=1 " + 
					" and i.Seqid = " + seqId + " ";
			
			
			Session session = getSession();
			result = (ItemInformation)session.createQuery(sqlString).list().get(0);
			session.close();
			
		} catch(Exception e) {
			logger.debug(e);
		}
		
		return result;
	}
}